﻿using System.Globalization;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using Aurouscia.TableEditor.Core.Utils;

namespace Aurouscia.TableEditor.Core.Excel
{
    public static class AuTableExcelConverter
    {
        public static AuTable? FromXlsx(Stream s, out string? errmsg)
        {
            return FromXlsx(s, null, out errmsg);
        }
        public static AuTable? FromXlsx(Stream s, AuTableExcelConverterOptions? options, out string? errmsg)
        {
            XSSFWorkbook book;
            try{ book = new(s); }
            catch
            {
                errmsg = "读取失败xlsx文件失败";
                return null;
            }
            s.Close();
            return FromXSSFWorkbook(book, options, out errmsg);
        }
        public static AuTable? FromXSSFWorkbook(XSSFWorkbook book, AuTableExcelConverterOptions? options, out string? errmsg)
        {
            options ??= new();
            if (book.NumberOfSheets < 1)
            {
                errmsg = "未找到任何工作簿";
                return null;
            }
            var sheet = book.GetSheetAt(0);

            if (sheet.LastRowNum + 1 > options.RowCountLimit)
            {
                errmsg = $"表格行数不应超过{options.RowCountLimit}";
                return null;
            }

            var evaluator = new XSSFFormulaEvaluator(book);
            evaluator.EvaluateAll();
            
            List<AuTableCellColorInfo?> colors = [];
            int charCount = 0;

            List<List<string?>?> cells = new();
            for (int rowIdx = 0; rowIdx <= sheet.LastRowNum; rowIdx++)
            {
                List<string?> buildingRow = new();
                var row = sheet.GetRow(rowIdx);
                if (row == null)
                    cells.Add(buildingRow);
                else
                {
                    if(row.LastCellNum > options.ColumnCountLimit)
                    {
                        errmsg = $"表格列数不应超过{options.ColumnCountLimit}";
                        return null;
                    }
                    for (int colIdx = 0; colIdx < row.LastCellNum; colIdx++)
                    {
                        var cell = row.GetCell(colIdx);
                        if (cell is null)
                            buildingRow.Add("");
                        else
                        {
                            string adding = "";
                            CellType type = cell.CellType;
                            if (cell.CellType == CellType.Formula)
                                type = cell.CachedFormulaResultType;
                            try
                            {
                                if (type == CellType.Numeric)
                                {
                                    if (DateUtil.IsCellDateFormatted(cell) && cell.DateCellValue is { } time)
                                    {
                                        adding = time.ToString("yyyy/MM/dd");
                                    }
                                    else
                                    {
                                        adding = Math.Round(cell.NumericCellValue, 2)
                                            .ToString(CultureInfo.InvariantCulture);
                                    }
                                }
                                else if (type == CellType.String)
                                    adding = cell.StringCellValue;
                                else if (type == CellType.Boolean)
                                    adding = cell.BooleanCellValue ? "✔" : "✘";

                                string? bgHex = null;
                                string? textHex = null;
                                var rgb = cell.CellStyle.FillForegroundColorColor?.RGB;
                                if (rgb is not null)
                                {
                                    bgHex = ColorUtil.ToHex([..rgb]);
                                }
                                XSSFFont? font = cell.CellStyle.GetFont(book) as XSSFFont;
                                if (font is not null)
                                {
                                    var fontColor = font.GetXSSFColor();
                                    if (fontColor is not null)
                                    {
                                        var fontRgb = fontColor.RGB;
                                        if (!ColorUtil.IsBlack([..fontRgb]))
                                            textHex = ColorUtil.ToHex([..fontRgb]);
                                    }
                                }
                                if (bgHex is {} || textHex is {})
                                {
                                    AuTableCellColorInfo colorInfo = new()
                                    {
                                        Pos = [rowIdx, colIdx],
                                        Bg = bgHex,
                                        Text = textHex
                                    };
                                    colors.Add(colorInfo);
                                }
                            }
                            catch
                            {
                                adding = "<读取失败>";
                            }
                            charCount += adding.Length;
                            if (charCount > options.CharCountLimit)
                            {
                                errmsg = $"表格字数超出限制({options.CharCountLimit})";
                                return null;
                            }
                            buildingRow.Add(adding);
                        }
                    }
                    cells.Add(buildingRow);
                }
            }

            var merges = new List<AuTableMergeInfo?>();
            var inputMerges = sheet.MergedRegions;
            inputMerges.ForEach(m =>
            {
                AuTableMergeInfo mergeInfo = new()
                {
                    Row = m.MinRow,
                    Col = m.MinColumn,
                    Right = m.MaxColumn - m.MinColumn,
                    Down = m.MaxRow - m.MinRow
                };
                merges.Add(mergeInfo);
            });
            errmsg = null;


            if (options.RemoveTrailingEmptyColumns && cells.Count > 0)
            {
                foreach (var row in cells)
                {
                    if(row is null)
                        continue;
                    int i = 0;
                    for (; i < row.Count; i++)
                    {
                        var c = row[row.Count - 1 - i];
                        if(!string.IsNullOrWhiteSpace(c))
                            break;
                    }
                    row.RemoveRange(row.Count - i, i);
                }
            }
            if (options.RemoveTrailingEmptyRows && cells.Count > 0)
            {
                int i = 0;
                for (; i < cells.Count; i++)
                {
                    var row = cells[cells.Count - 1 - i];
                    if(row is not null && row.Any(x => !string.IsNullOrWhiteSpace(x)))
                        break;
                }
                cells.RemoveRange(cells.Count - i, i);
            }

            var res = new AuTable()
            {
                Cells = cells,
                Merges = merges,
                Colors = colors
            };
            res.FillToRect();

            return res;
        }
        public static Stream? ToXlsx(this AuTable table, out string? errmsg)
        {
            var book = table.ToXSSFWorkbook(out errmsg);
            if (book is not null)
            {
                Stream res = new MemoryStream();
                book.Write(res, true);
                res.Position = 0;
                return res;
            }
            return null;
        }
        public static XSSFWorkbook? ToXSSFWorkbook(this AuTable table, out string? errmsg)
        {
            try
            {
                XSSFWorkbook book = new();
                if (table.Cells is not null)
                {
                    var sheet = book.CreateSheet("sheet1");
                    for (int r = 0; r < table.Cells.Count; r++)
                    {
                        var row = sheet.CreateRow(r);
                        List<string?>? dataCol = table.Cells[r];
                        if (dataCol is null)
                            continue;
                        for (int c = 0; c < dataCol.Count; c++)
                        {
                            var cell = row.CreateCell(c);
                            cell.SetCellType(CellType.String);
                            cell.SetCellValue(dataCol[c] ?? "");
                        }
                    }

                    if (table.Merges is not null)
                    {
                        foreach (var m in table.Merges)
                        {
                            if (m is not null)
                                sheet.AddMergedRegion(new(m.Row, m.Row + m.Down, m.Col, m.Col + m.Right));
                        }

                        sheet.ValidateMergedRegions();
                    }
                }

                errmsg = null;
                return book;
            }
            catch (Exception ex)
            {
                errmsg = ex.Message;
                return null;
            }
        }
    }
}
